# -*- coding: utf-8 -*-

from wt.connector.db_manager.db_untils_tools import DBUntilsTools
from wt.connector.data_connect import DataConnect


class Create04SqlsData(DataConnect, DBUntilsTools):

    def __init__(self, ):
        super().__init__()

    def define_col(self):
        """
        定义sqlserver 插入字段类型
        :return:
        """
        id = self.get_incre_time()
        col = {
            "id": "int primary key identity,",
            "c_02_integer_col": "int NULL,",
            "c_03_smallint_col": "smallint NULL,",
            "c_04_bigint_col": "bigint NULL,",
            "c_05_double_col": "decimal(10,2) NULL,",
            "c_06_float_col": "float NULL,",
            "c_07_decimal_col": "decimal(10,2) NULL,",
            "c_08_date_col": "date NULL,",
            "c_09_time_col": "time NULL,",
            "c_10_timestamp_col": "datetime2 NULL,",
            "c_11_char_col": "varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,",
            "c_12_char_col": "NVARCHAR(50),",
            "": ")"
        }
        return col

    def large_ddl(self, db, schema, table):
        col = {
            "id": "int primary key identity,",
            "c_01": "nvarchar(MAX) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,",
            "c_02": "tinyint NULL,",
            "c_03": "tinyint NULL,",
            "c_04": "smallint NULL,",
            "c_05": "int NULL,",
            "c_06": "bigint NULL,",
            "c_07": "float NULL,",
            "c_08": "float NULL,",
            "c_09": "decimal(30, 8) NULL, ",
            "c_10": "date NULL,",
            "c_11": "datetime NULL,",
            "": ");"
        }
        ddl_sql = 'CREATE TABLE {}.{}.{} ( \r\n'.format(db, schema, table)
        for key in col:
            line = key + " " + col[key] + "\r\n"
            ddl_sql = ddl_sql + line
        self.exec_sqlserver_sql(db, "drop table {}.{}.{}".format(db, schema, table))
        self.exec_sqlserver_sql(db, ddl_sql)
        return col

    def define_ddl(self, db, schemaName, tableName):
        """
        拼接oracle ddl语句
        :param table:
        :return:
        """
        col = self.define_col()
        ddl_sql = 'CREATE TABLE {}.{} ( \r\n'.format(schemaName, tableName)
        for key in col:
            line = key + " " + col[key] + "\r\n"
            ddl_sql = ddl_sql + line
        return ddl_sql

    def generate_dml_sql(self, databaseName, schemaName, tableName, num):
        """
        批量生成 SQL Server DML 语句
        :param database: 数据库名称
        :param table: 表名称
        :param num: 生成的记录数量
        :return: 插入的 SQL 语句
        """
        # 拼接 SQL 表头
        insert_sql = "INSERT INTO {}.{} (c_02_integer_col, c_03_smallint_col, c_04_bigint_col, c_05_double_col," \
                     " c_06_float_col, c_07_decimal_col, c_08_date_col, c_09_time_col, c_10_timestamp_col," \
                     " c_11_char_col, c_12_char_col) VALUES ".format(schemaName, tableName)

        res_num = self.get_count(databaseName, schemaName, tableName)

        values_list = []
        for i in range(res_num + 1, res_num + num + 1):
            c_02_integer_val = self.get_random(-2147483648, 2147483647)
            c_03_smallint_val = self.get_random(-32768, 32767)
            c_04_bigint_val = self.get_random(-9223372036854775808, 9223372036854775807)
            c_05_double_val = self.get_random_float(-500, 500)
            c_06_float_val = self.get_random_float(-500, 500)
            c_07_decimal_val = self.get_random_float(-32768, 32767)
            c_08_date_val = "'{}'".format(self.get_random_date())  # 确保日期格式正确
            c_09_time_val = "'{}'".format(self.get_random_time())  # 使用有效的时间格式
            c_10_timestamp_val = "'{}'".format(self.get_random_datetime())  # 确保时间戳格式正确
            c_11_char_val = "'{}'".format(self.get_random_string(30))
            c_12_char_val = "'{}'".format(self.get_random_string(30))

            values_list.append("({}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {})".format(
                c_02_integer_val,
                c_03_smallint_val,
                c_04_bigint_val,
                c_05_double_val,
                c_06_float_val,
                c_07_decimal_val,
                c_08_date_val,
                c_09_time_val,
                c_10_timestamp_val,
                c_11_char_val,
                c_12_char_val
            ))

        insert_sql += ", ".join(values_list) + ";"  # 语句结束时加分号

        return insert_sql

    def create_and_insert_sql(self, db, table, num, is_recreate=None):
        """
        生成ddl、插入dml
        :param is_recreate:
        :param db:
        :param table:
        :param num:
        :return:
        """
        table = db + ".dbo." + table
        if is_recreate:
            ddl_sql = self.define_ddl(db, table)
            self.exec_sqlserver_sql(db, "drop table {}".format(table))
            self.exec_sqlserver_sql(db, ddl_sql)
        insert_sql = self.generate_dml_sql(db, table, num)
        # print(insert_sql)
        for line in insert_sql.strip().split("\r\n"):
            self.exec_sqlserver_sql(db, line)

    def get_count(self, databaseName, schemaName, tableName):
        sql = "select count(1) as cot from {}.{}.{}".format(databaseName, schemaName, tableName)
        res = self.exec_sqlserver_sql(databaseName, sql)
        print(res)
        if res['message'] == "Success":
            res_num = res['data'][0][0]
            return res_num
        else:
            return None

    def execute_drop_table(self, databaseName, schemaName, tableName):
        sql = "drop table {}.{}.{}".format(databaseName, schemaName, tableName)
        res = self.exec_sqlserver_sql(databaseName, sql)
        return self.set_return_massage(res)

    def cdc_set_on(self, databaseName, schemaName, tableName):
        cdc_sql = ("exec sys.sp_cdc_enable_table @source_schema = N'{}', @source_name = N'{}', @role_name = NULL, "
                 "@supports_net_changes = 0").format(schemaName, tableName)
        return self.exec_sqlserver_sql(databaseName, cdc_sql)

    def execute_deleted_data(self, databaseName, schemaName, tableName):
        sql = ("delete FROM {}.{}.{} WHERE id IN ( SELECT TOP 100 id FROM {}.{}.{} ORDER BY id DESC);".
               format(databaseName, schemaName, tableName, databaseName, schemaName, tableName))
        res = self.exec_sqlserver_sql(databaseName, sql)
        print(res)
        if res['message'] == "Success":
            return True
        else:
            return False

    def execute_update_data(self, databaseName, schemaName, tableName):
        sql1 = ("select COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '{}' AND TABLE_SCHEMA = '{}';"
                .format(tableName, schemaName))

        res = self.exec_sqlserver_sql(databaseName, sql1)
        if res['message'] != "Success":
            return False

        columns = [row[0] for row in res['data'] if row[0] != 'id']

        get_ids_sql = "select TOP 10 id FROM {}.{}.{} ORDER BY id DESC;".format(databaseName, schemaName, tableName)
        ids_res = self.exec_sqlserver_sql(databaseName, get_ids_sql)
        if ids_res['message'] != "Success":
            return False

        ids = [row[0] for row in ids_res['data']]

        for id in ids:
            updates = []
            for col in columns:
                if col == 'c_02_integer_col':
                    updates.append("{} = {}".format(col, self.get_random(-2147483648, 2147483647)))
                elif col == 'c_03_smallint_col':
                    updates.append("{} = {}".format(col, self.get_random(-32768, 32767)))
                elif col == 'c_04_bigint_col':
                    updates.append("{} = {}".format(col, self.get_random(-9223372036854775808, 9223372036854775807)))
                elif col == 'c_05_double_col':
                    updates.append("{} = {}".format(col, self.get_random_float(-500, 500)))
                elif col == 'c_06_float_col':
                    updates.append("{} = {}".format(col, self.get_random_float(-500, 500)))
                elif col == 'c_07_decimal_col':
                    updates.append("{} = {}".format(col, self.get_random_float(-32768, 32767)))
                elif col == 'c_08_date_col':
                    updates.append("{} = '{}'".format(col, self.get_random_date()))  # 直接使用字符串格式
                elif col == 'c_09_time_col':
                    updates.append("{} = '{}'".format(col, self.get_random_time()))
                elif col == 'c_10_timestamp_col':
                    updates.append("{} = '{}'".format(col, self.get_random_datetime()))  # 直接使用字符串格式
                elif col == 'c_11_char_col':
                    updates.append("{} = '{}'".format(col, self.get_random_string(30)))
                elif col == 'c_12_char_col':
                    updates.append("{} = '{}'".format(col, self.get_random_string(30)))

            update_sql = "UPDATE {}.{} SET {} WHERE id = {}".format(schemaName, tableName, ", ".join(updates), id)
            update_res = self.exec_sqlserver_sql(databaseName, update_sql)
            print(update_res)
            if update_res['message'] != "Success":
                return False

        return True

    def get_db_data(self, databaseName, schemaName, tableName):
        sql = "select TOP 10 * FROM {}.{}.{} ORDER BY id DESC;".format(databaseName, schemaName, tableName)
        res = self.exec_sqlserver_sql(databaseName, sql)
        return self.set_return_massage(res)


if __name__ == '__main__':
    a = Create04SqlsData()
    db = "large_data"
    schema = "dbo"
    # for tmp in range(100):
    #     table = "whale_test_table_"
    #     i=tmp+1
    #     table = table+str(i)
    #     #print(table)
    #     a.large_ddl(db, schema, table)
    #     a.cdc_set_on(db, schema, table)
    #
    muti_sql = ""
    for tmp in range(100):
        table = "whale_test_table_"
        i = tmp + 1
        table = table + str(i)
        sql= "INSERT INTO large_data.dbo.{} (c_01, c_02, c_03, c_04, c_05, c_06, c_07, c_08, c_09, c_10, c_11) VALUES('1', 0, 0, 0, 0, 0, 0, 0, 0, '', '');{}".format(table, "\n")
        muti_sql += sql
    print(muti_sql)

